package com.weishenai.customerassistant.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.weishenai.customerassistant.common.dto.MeterFactFinderStatisticsDto;
import com.weishenai.customerassistant.common.dto.MeterFactFinderWeekStatisticsDto;
import com.weishenai.customerassistant.common.dto.MeterTypeStatisticsDto;
import com.weishenai.customerassistant.entity.MetersResult;
import org.apache.ibatis.annotations.Select;

import java.util.List;

import com.weishenai.customerassistant.common.dto.MeterResultStatisticsDto;

public interface MetersResultMapper extends BaseMapper<MetersResult> {

    @Select("SELECT substation_name,count(DISTINCT fact_finder) as factNum FROM `meters_result` group by substation_name")
    List<MeterResultStatisticsDto> listMeterResultStatistics();

    @Select("SELECT\n" +
            "\tmaterial_type,\n" +
            "\tcount( 'material_type' ) AS materialTypeNum \n" +
            "FROM\n" +
            "\t`meters_result` \n" +
            "WHERE\n" +
            "\tsubstation_name = '${substation}' \n" +
            "\tAND created_time BETWEEN '${startTime}' \n" +
            "\tAND '${endTime}' \n" +
            "GROUP BY\n" +
            "\tmaterial_type")
    List<MeterTypeStatisticsDto> listMeterTypeStatisticsBySubstation(String substation, String startTime, String endTime);

    /**
     * 返回当前供电所的每个人员的工作量
     *
     * @param substation 供电所名称
     * @return
     */
    @Select("SELECT\n" +
            "\tfact_finder,\n" +
            "\tcount( 'fact_finder' ) AS factFinderJobNum \n" +
            "FROM\n" +
            "\t`meters_result` \n" +
            "WHERE\n" +
            "\tsubstation_name = '${substation}' \n" +
            "\tAND created_time BETWEEN '${startTime}' \n" +
            "\tAND '${endTime}' \n" +
            "GROUP BY\n" +
            "\tfact_finder")
    List<MeterFactFinderStatisticsDto> listFactFinderStatisticsBySubstation(String substation, String startTime, String endTime);

    @Select("select a.click_date,ifnull(b.count,0) as jobCount\n" +
            "from (\n" +
            "    SELECT curdate() as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 1 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 2 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 3 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 4 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 5 day) as click_date\n" +
            "    union all\n" +
            "    SELECT date_sub(curdate(), interval 6 day) as click_date\n" +
            ") a left join (\n" +
            "  select DATE_FORMAT(created_time,'%Y-%m-%d') as datetime, count(*) as count\n" +
            "  from \t`meters_result`\n" +
            "\tWHERE `substation_name` = '${substation}' \n" +
            "\tAND `fact_finder` = '${fact}'\n" +
            "  group by `fact_finder`, DATE_FORMAT(created_time,'%Y-%m-%d') \n" +
            ") b on a.click_date = b.datetime;")
    List<MeterFactFinderWeekStatisticsDto> getFactFinderWeekJob(String substation, String fact);
}
